package com.assignment.database;
/**
 * 	Database Connection Class
 * 
 * @author John Wright
 * @version 1.0
 *                                                                                                                                                                                                                                                                                                                                                                                                    
 */


import java.awt.image.BufferedImage;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.sql.Connection;




import java.sql.Blob;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import javax.imageio.ImageIO;
import javax.swing.ImageIcon;

public class Database {

	private static String username, password, dbPath; 
	private static Connection connection;

	// Username, Password, database path eg "localhost/Assignment"
	public static void initialise(String user, String pass, String path) {

		username = user;
		password = pass;
		dbPath = path;


	}

	// Connects to the database; This method must be run first in order connect
	// Username, Password, database path eg "localhost/Assignment"
	// returns true if successful and false if not
	public static boolean connect() {

		try {
			connection = DriverManager
					.getConnection("jdbc:mysql://" + dbPath, username, password);

			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}

	}
	
	// Performs a commit on the database, saving the data
	public static boolean commit() {
		
		try {
			connection.commit();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return false;
		}
	}
	
	// Performs a rollback on the database, going back to the previous commit
		public static boolean rollback() {
			
			try {
				connection.rollback();
				return true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return false;
			}
		}
	
	
	// Closes the connection to the database
	public static boolean close() {
		
		try {
			connection.close();
			return true;
		}
		catch(SQLException e) {
			e.printStackTrace();
			return false;
		}
	}
	
	
	// Executes an SQL statement where nothing is returned
	// returns whether successful or not
	public static boolean doSQL(String query) {
		
		
		Statement stmt;

		try {
			stmt = connection.createStatement();
			stmt.executeUpdate(query);
			return true;
		}catch (Exception e) {
			//e.printStackTrace();
			System.out.println("doSQL Error");
			return false;
		}
		
	}

	// Returns the result of a query as a 2D String array
	public static String[][] returnQuery(String query) {

		StringBuilder output = new StringBuilder();
		Statement stmt;

		try {
			stmt = connection.createStatement();
			ResultSet rs = stmt.executeQuery(query);
			int rowCount = 0;

			while (rs.next()) {
				for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
					output.append(rs.getString(i) + "~");
				}
				output.append("\n");
				rowCount++;
			}

			String[][] result = new String[rs.getMetaData().getColumnCount()][rowCount];

			String s[] = output.toString().split("~");

			int i = 0;
			for (int y = 0; y < result[0].length; y++) {
				for (int x = 0; x < result.length; x++) {

					result[x][y] = s[i];
					i++;
				}
			}

			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}

	}
	
	// Returns the result of a query as a 2D String array
		public static String[][] returnQueryNoImage(String query) {

			StringBuilder output = new StringBuilder();
			Statement stmt;

			try {
				stmt = connection.createStatement();
				ResultSet rs = stmt.executeQuery(query);
				int rowCount = 0;

				while (rs.next()) {
					for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
						if (i==9) {
							output.append(" ~");
						}else{
						output.append(rs.getString(i) + "~");
						}
					}
					output.append("\n");
					rowCount++;
				}

				String[][] result = new String[rs.getMetaData().getColumnCount()][rowCount];

				String s[] = output.toString().split("~");

				int i = 0;
				for (int y = 0; y < result[0].length; y++) {
					for (int x = 0; x < result.length; x++) {

						result[x][y] = s[i];
						i++;
					}
				}

				return result;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				return null;
			}

		}
		

	// Prints a query result in the form of a 2D String array to the console; Useful for debugging?
	public static void printQuery(String[][] s) {

		for (int y = 0; y < s[0].length; y++) {
			for (int x = 0; x < s.length; x++) {

				System.out.print(s[x][y] + "\t");
			}
			System.out.println();
		}

	}
	
	public static ImageIcon getImage(String id) {
		Statement stmt;
		connect();
		id = id.replaceAll("\n","").trim();
		try {
			stmt = connection.createStatement();

			ResultSet rs = stmt.executeQuery(String.format("select image from item where PK_Item_ID='%s';",id));
			
			Blob imageBlob;
			byte[] data = null;
			
			while(rs.next()) {
				
			imageBlob = rs.getBlob(1);

			 data = imageBlob.getBytes(1, (int) imageBlob.length());
			
			}
			
			 BufferedImage img = ImageIO
						.read(new ByteArrayInputStream(data));
			 ImageIcon ii = new ImageIcon(img);
			 
			 close();
			 return ii;
				
			
		} catch (Exception e) {
			System.out.println("getImage error" );
			//e.printStackTrace();
		}
		close();
		return null;

	}
	
	public static void insertImage(String id, File image) {
		try {
			id = id.replaceAll("\n", "").trim();
			connect();
			PreparedStatement psmnt = connection
					.prepareStatement("update item set image=? where item.PK_Item_ID=?");
							
			psmnt.setString(2, id);
			FileInputStream fis = new FileInputStream(image);
			psmnt.setBinaryStream(1, (InputStream) fis, (int) (image.length()));

			/*
			 * executeUpdate() method execute specified sql query. Here this
			 * query insert data and image from specified address.
			 */
			int s = psmnt.executeUpdate();
			if (s > 0) {
				System.out.println("Uploaded successfully !");
			} else {
				System.out.println("unable to upload image.");
			}
		} catch (Exception e) {
			//e.printStackTrace();
			System.out.println("Insert Image error");
		}
		
		close();

	}
	
	
}
